###############################################################################
###############################################################################
############# DATABASE ACCESS FUNCTIONS FOR CellProfile-R ######################
###############################################################################
# Author: Amos A Folarin (amosfolarin@gmail.com)
# Description: The database tables provided by CellProfiler are available in 3 
# formats, MySQL, SQLite and CSV (comma separated files). I have tried where 
# possible to abstract the different database formats for the end user so once
# read into R this should be more transparent.
# History:
#       - version 1.0
#       - 
###############################################################################
###############################################################################


###############################################################################
### chunk number mySqlConnect:
# mysql database connect - returns connection to the mysql database created
#    by CellProfiler ExportToDatabase module (see: option mysql).

#
# @arguements hostname = database host name, default localhost
# @arguements database.name = the name of the database generated by CellProfiler
# @arguements database.user = the database username
# @arguements databasePassword = the database password for database.user
# @value returns a connection to the named database
# e.g.
# database.connection <- mySqlConnect("localhost", "db_name", "db_username", "db_password")
###############################################################################
mySqlConnect <- function(hostname="localhost", database.name, database.user
, database.password)
{
    if(is.null(hostname))
    {
        stop("hostname == NULL")
    }
    else if(is.null(database.name))
    {
        stop("hostname == NULL")
    }
    else if(is.null(database.name))
    {
        stop("database.name == NULL")
    }
    else if(is.null(database.user))
    {
        stop("database.user == NULL")
    }
    else if(is.null(database.password))
    {
        stop("databasePassword == NULL")
    }
    else
    {
        drv <- dbDriver("MySQL")
        con <- dbConnect(drv,host=hostname, dbname=database.name
        , user=database.user, pass=database.password)
        return(con)
    }
}




###############################################################################
### chunk number: sqLiteConnect
# SQLite database connect - returns connection to the SQLite database created 
#    by CellProfiler ExportToDatabase module (see: option mysqlite).
#
# @arguements database.file = the name of the SQLite database file generated by 
#   CellProfiler (e.g. *.db) include the path where not in the working directory.
# @value returns a conection object to the sqlite database file.
# e.g.
# con <- sqLiteConnect(database.file="<path>/g144_db.db")
###############################################################################
sqLiteConnect <- function(database.file)
{
    # create a SQLite instance and create one connection.
    drv <- dbDriver("SQLite")
    
    ##insert some filechecking for the database file
    checkValidFilenames(database.file)
    
    con <- dbConnect(drv, dbname = database.file)
    return(con)
}




###############################################################################
### chunk number: fileConnect 
# Provides an abstraction to a csv database output by the ExportToSpreadSheet 
#   CellProfiler Module e.g image_table and object_table csv file.
#
# @arguements directory = absoloute path to the directory with the csv table 
#   files from cellprofiler, it's required that all files use the same directory 

# @arguements table.filenames = the csv filename for both per_image 
#   and per_object files e.g.
#   c("per_image"="DefaultOUT_Image.csv", "per_object"="DefaultOUT_cell.csv").
#   Additional tables to include in the connection may be spcified here with the
#   same "key"="filepath" format.
#   Note, a "per_class" table is however required if you want to use 
#   createUnifiedTable see(createUnifiedTable).
# @value a named vector
# e.g. 
# fcon <- fileConnect(directory="<path>/cellprofilerOutputDir", table.filenames=c("per_image"="DefaultOUT_Image.csv", "per_object"="DefaultOUT_CellAndNuclei.csv"))
###############################################################################
fileConnect <- function(directory, table.filenames=c("per_image"="", "per_object"="", "per_class"=""))
{
    filepaths <- paste(directory, table.filenames, sep="/")
    names(filepaths) <- names(table.filenames)
    
    #filepaths <- chartr("\\", "/", filepaths) #basic delimiter checking
    #directory <- chartr("\\", "/", directory)
    
    if(filepaths["per_image"] == "" || filepaths["per_object"] == "")
    {
        stop("Require both per_image and per_object as a minimum")
    }
    
    #read.table ... return a data.frame
    n.list = NULL
    
    for(i in seq(along=filepaths))
    {
        ##insert some filechecking for the database file
        checkValidFilenames(filepaths[i])
         
        n.list[names(filepaths[i])] <- filepaths[i]
    }
       
    
    n.list["directory"] <- directory
    
    return(n.list)
    
    
}





###############################################################################
### chunk number : loadTable
# Read in to R all or specified columns of the a given database table. 
#
#
# @arguements database.connection = a connection object returned from e.g. from 
#   mySQLConnect, mySQLiteConnect, fileConnect.
# @arguements table.name = character vector name of the the table to load e.g. 
#   "per_image", or for csv files the name of the file.
# @arguements column.names = a list of column names to load from table.
# @value return the subset of table.name as a dataframe.
# e.g. whole table
# per.image <- loadTable(database.connection, table.name="per_image")
# e.g. load subset
# per.obj <- loadTable(database.connection, table.name="per_object", column.names=c("CellAndNuclei_AreaShape_Area", "CellAndNuclei_AreaShape_Eccentricity", "CellAndNuclei_AreaShape_EulerNumber"))
###############################################################################
loadTable <- function(database.connection,table.name, column.names=NULL)
{
    toggle <- class(database.connection)[1]
    
    if(toggle == "MySQLConnection")
    {
        if(is.null(database.connection))
        {
            stop("supplied database connection is not valid")
        }
        else if(is.null(column.names))
        {
               tab <- dbGetQuery(database.connection,statement=paste("select * from ", table.name))  
               return(tab)
        
        }
        else
        {
            tab <- dbGetQuery(database.connection, statement=paste("select ", paste(column.names, collapse=", "), " from ", table.name))  
            return(tab)
        }
    }
    else if(toggle == "SQLiteConnection")
    {
        if(is.null(database.connection))
        {
            stop("supplied database connection is not valid")
        }
        else if(is.null(column.names))
        {
               tab <- dbGetQuery(database.connection,statement=paste("select * from ", table.name))  
               return(tab)
        
        }
        else
        {
            tab <- dbGetQuery(database.connection, statement=paste("select ", paste(column.names, collapse=", "), " from ", table.name))  
            return(tab)
        }
    }
    else if(toggle == "character")
    {
        #load the specified table..
        tab <- read.csv(database.connection[table.name], stringsAsFactors=FALSE, header=TRUE)
        if(!is.null(column.names))
        {
            return(tab[, column.names]) ##return col subset
        }
        else
        {
            return(tab) ## return whole 
        }
    }
    
}

 

###############################################################################
### chunk number : createUnifiedTable
# Create a joined per_object, per_image, class_table, 
# the columns are merged on the ImageNumber primarily, and objects secondly on 
#   the ObjectNumber;
# this is written to the database, where it can be read in with loadTable.
# The table's hardcoded name is "unified_table". 
# Note: you will require to have created a class_table
# which requires that you have run 
# CellProfiler Analyst Classifier (see the properties file "class_table"), this
# option may not yet be available if you are using SQLite or CSV database 
#   options.
# The unified_table will typically have all columns from per_object, a specified
# number of columns from per_image and always class_table 
#   ("class" and "class_number") columns.
# 
#
# @arguements database.connection = a connection object returned from 
#   mySqLiteConnect, sqLiteConnect, fileConnect.
# @arguements class.table.name = the name given to the class table in the 
#   CellProfiler Properties file, or if using csv then use the filename.
# @arguements per.object.cols = character vector of per_object table columns
#   to include in unified table, defaults to all columns if not specified.
# @arguements per.image.cols =  character vector of per_image table columns to 
#   include in unified table, defaults to "Image_Group_Index", 
#   "Image_Group_Number" if not specified. If using CellProfiler METADATA 
#   capture, you will most likely want to include these columns!
# @value returns if successful an array of the unified table columns. 
#   The function however creates a new table in the database), you can read 
#   this table in with loadUnifiedTable and loadSubsetUnifiedTable
# e.g.
# need something like this: "CREATE TABLE unified_table  SELECT per_object.*, per_class.class, per_class.class_number, per_image.Image_Metadata_Hour, per_image.Image_Metadata_Plate, per_image.Image_Metadata_Well, per_image.Image_PathName_Phase, per_image.Image_FileName_Phase  FROM (per_class LEFT JOIN per_object USING (ImageNumber, ObjectNumber)) LEFT JOIN per_image  USING (ImageNumber)"
#
# createUnifiedTable(con, "per_class", per.image.cols=c("Image_Metadata_Hour", "Image_Metadata_Plate", "Image_Metadata_Well", "Image_PathName_Phase","Image_FileName_Phase"))
#


## TODO: test unify mysqlite db, csv version unify...

###############################################################################
createUnifiedTable <- function(database.connection, class.table.name, per.object.cols=c("*"), per.image.cols=c("*"))
{
    # check if the class table exists  #TODO
    
    
    #the class table columns. fixed
    class.table.cols <- c("class", "class_number")
    
    if(is.null(database.connection))
    {
        stop("supplied database connection is not valid. please specify a database connection (see help)")
    }
    else if(is.null(class.table.name))
    {
        stop("class.table.name==NULL. please specify the name of the class table (see help)")
    }
    
    
    ##------------------------------ join tables ----------------------------
    
    # switch on type of connection
    toggle <- class(database.connection)[1]
    
    if(toggle == "MySQLConnection")
    {
        #e.g. this works
        #cmd <- paste("CREATE TABLE unified_table  SELECT per_object.*, ",class.table.name,".class, ",class.table.name,".class_number, per_image.Image_Count_CellAndNuclei, per_image.Image_FileName_Phase, per_image.Image_Group_Index, per_image.Image_Group_Number, per_image.Image_MD5Digest_Phase, per_image.Image_Metadata_Hour, per_image.Image_Metadata_ImagesPerWell, per_image.Image_Metadata_Plate, per_image.Image_Metadata_Well, per_image.Image_PathName_Phase  FROM (",class.table.name," LEFT JOIN per_object USING (ImageNumber, ObjectNumber)) LEFT JOIN per_image  USING (ImageNumber)", sep="")
        
        
        ## some rudimentary checking to make sure ImageNumber is not duplicated in the column selections, otherwise JOIN will fail.
        if(per.image.cols == "*" && per.object.cols == "*")
        {
            print("NOTE: Joining the whole per_image and per_object, this will be a fairly large database table!")
            
            per.image.cols <- dbGetQuery(database.connection, statement="DESCRIBE per_image")$Field
            per.object.cols <- dbGetQuery(database.connection, statement="DESCRIBE per_object")$Field
            
            #strike all redundant columns from the per.image.col arg - the primary objective here is to remove the "ImageNumber" which is always in both
            per.image.cols <- per.image.cols[ !(per.image.cols %in% per.object.cols) ]
            
        }
        else if(per.object.cols == "*") #check if ImageNumber is specified in per.image.cols
        {
            per.object.cols <- dbGetQuery(database.connection, statement="DESCRIBE per_object")$Field
            #strike redundant columns from the list in the per.image.col arg
            per.image.cols <- per.image.cols[ !(per.image.cols %in% per.object.cols) ]
        }
        else if(per.image.cols == "*") # check if ImageNumber is specified in per.object.cols
        {
            per.image.cols <- dbGetQuery(database.connection, statement="DESCRIBE per_image")$Field
            #strike redundant columns from the list in the per.object.col arg
            per.object.cols <- per.object.cols[ !(per.object.cols %in% per.image.cols) ]
        }
        else  ## specific per.image.cols and per.object.cols given, check for redundancy between table names
        {
             #strike all redundant columns from the per.image.col arg - the primary objective here is to remove the "ImageNumber" which is always in both
            per.image.cols <- per.image.cols[ !(per.image.cols %in% per.object.cols) ]
        }
         
        ## if unified_table exists give opt-out message
        tab.exists <- dbGetQuery(database.connection, statement="show tables like 'unified_table'")[1,1] 
        if(!is.null(tab.exists) && tab.exists == "unified_table")
        {
            #ask user if they want to overwrite the table. y/n
            cat("\n\n", "WARNING: A table called \"unified_table\" already exists in the named database, do you want to overite?", "\n", "Select (1) yes=to overwrite or (2) no=to exit.")
            choice <- menu(c("yes", "no"))
            
            if(choice == 2 || choice == 0)
            {
                stop("Please verify that you want to overwrite the existing unified_table, before running this again...")
                return(NULL)
            }
        }
        
        
        ## execute the MySQL JOIN
        cmd <- paste("CREATE TABLE unified_table  SELECT ", paste(paste("per_object.", per.object.cols, sep="", collapse=", "), ", ", sep=""),class.table.name,".class, ",class.table.name,".class_number, ", paste("per_image.", per.image.cols, sep="", collapse=", "), "  FROM (",class.table.name," LEFT JOIN per_object USING (ImageNumber, ObjectNumber)) LEFT JOIN per_image  USING (ImageNumber)", sep="")
        
        dbGetQuery(database.connection, statement=cmd)
        #Verify that table was created.. return table structure
        return(dbGetQuery(database.connection, statement="DESCRIBE unified_table"))
        
    }
    else if(toggle == "SQLiteConnection")
    {
        #NOTE: this may or may not be supported yet in cellprofiler(?)
        
        #Check if the class_table is generated or not. #TODO
        
        
        
        ## some rudimentary checking to make sure ImageNumber is not duplicated in the column selections, otherwise JOIN will fail.
        if(per.image.cols == "*" && per.object.cols == "*")
        {
            print("NOTE: Joining the whole per_image and per_object, this will be a fairly large database table!")
            
            per.image.cols <- colnames(dbGetQuery(database.connection, statement="SELECT * FROM per_image LIMIT 1"))
            per.object.cols <- colnames(dbGetQuery(database.connection, statement="SELECT * FROM per_object LIMIT 1"))
            
            #strike all redundant columns from the per.image.col arg - the primary objective here is to remove the "ImageNumber" which is always in both
            per.image.cols <- per.image.cols[ !(per.image.cols %in% per.object.cols) ]
            
        }
        else if(per.object.cols == "*") #check if ImageNumber is specified in per.image.cols
        {
             per.object.cols <- colnames(dbGetQuery(database.connection, statement="SELECT * FROM per_object LIMIT 1"))
            #strike redundant columns from the list in the per.image.col arg
            per.image.cols <- per.image.cols[ !(per.image.cols %in% per.object.cols) ]
        }
        else if(per.image.cols == "*") # check if ImageNumber is specified in per.object.cols
        {
            per.image.cols <- colnames(dbGetQuery(database.connection, statement="SELECT * FROM per_image LIMIT 1"))
            #strike redundant columns from the list in the per.object.col arg
            per.object.cols <- per.object.cols[ !(per.object.cols %in% per.image.cols) ]
        }
        else  ## specific per.image.cols and per.object.cols given, check for redundancy between table names
        {
             #strike all redundant columns from the per.image.col arg - the primary objective here is to remove the "ImageNumber" which is always in both
            per.image.cols <- per.image.cols[ !(per.image.cols %in% per.object.cols) ]
        }
         
        ## if unified_table exists give opt-out message
        table.n <- dbGetQuery(database.connection, statement="SELECT name FROM sqlite_master")
        tab.exists <-  sum(table.n %in% "unified_table")
        if(tab.exists > 0)
        {
            #ask user if they want to overwrite the table. y/n
            cat("\n\n", "WARNING: A table called unified_table already exists in the named database, do you want to ovewrite it?", "\n", "Select (1) yes=to overwrite or (2) no=to exit.")
            choice <- menu(c("yes", "no"))
            
            if(choice == 2 || choice == 0)
            {
                stop("Please verify that you want to overwrite the existing unified_table, before running this again...")
                return(NULL)
            }
        }
        
        
        ## execute the MySQL JOIN
        cmd <- paste("CREATE TABLE unified_table AS  SELECT ", paste(paste("per_object.", per.object.cols, sep="", collapse=", "), ", ", sep=""),class.table.name,".class, ",class.table.name,".class_number, ", paste("per_image.", per.image.cols, sep="", collapse=", "), "  FROM (",class.table.name," LEFT JOIN per_object USING (ImageNumber, ObjectNumber)) LEFT JOIN per_image  USING (ImageNumber)", sep="")
        
        
        
        dbGetQuery(database.connection, statement=cmd) ### join failing...... ## TODO
        #Verify that table was created.. return table structure
        return(colnames(dbGetQuery(database.connection, statement="SELECT * FROM unified_table LIMIT 1")))
        
        
        
        # clean up
        #dbDisconnect(con)
        #file.info(tfile)
        #file.remove(tfile)
        
        
        
        
        
        
    }
    else if(toggle == "character")
    {
        #NOTE: this may or may not be supported yet in cellprofiler(?) 
        
        ## check if the class_table is generated or not.  #TODO
        
        ## nb. can't use file(..) as need to specify directory rather than specific file, better abstraction of the database as multiple tables in separate csv files
        
        ## unify from csv connection
        per.image <- read.table(database.connection["per_image"])
        per.object <- read.table(database.connection["per_object"])
        class.table <- read.table(database.connection["per_class"])
        
        ## some rudimentary checking to make sure ImageNumber is not duplicated in the column selections, otherwise JOIN will fail.
        if(per.image.cols == "*" && per.object.cols == "*")
        {
            print("NOTE: Joining the whole per_image and per_object, this will be a fairly large database table!")
            
            per.image.cols <- colnames(per.image)
            per.object.cols <- colnames(per.object)
            
            #strike all redundant columns from the per.image.col arg - the primary objective here is to remove the "ImageNumber" which is always in both
            per.image.cols <- per.image.cols[ !(per.image.cols %in% per.object.cols) ]
            
        }
        else if(per.object.cols == "*") #check if ImageNumber is specified in per.image.cols
        {
            per.object.cols <- colnames(per.object)
            #strike redundant columns from the list in the per.image.col arg
            per.image.cols <- per.image.cols[ !(per.image.cols %in% per.object.cols) ]
        }
        else if(per.image.cols == "*") # check if ImageNumber is specified in per.object.cols
        {
            per.image.cols <- colnames(per.image)
            #strike redundant columns from the list in the per.object.col arg
            per.object.cols <- per.object.cols[ !(per.object.cols %in% per.image.cols) ]
        }
        else  ## specific per.image.cols and per.object.cols given, check for redundancy between table names
        {
             #strike all redundant columns from the per.image.col arg - the primary objective here is to remove the "ImageNumber" which is always in both
            per.image.cols <- per.image.cols[ !(per.image.cols %in% per.object.cols) ]
        }
        
        #do the join
        tmp <- merge(per.object, class.table, by.x=c("ImageNumber", "ObjectNumber"), by.y=c("ImageNumber", "ObjectNumber"))
        unified.table <- merge(tmp, per.image, by.x=c("ImageNumber"), by.y=c("ImageNumber"))
        
        #select the non-redundant column set
        unified.table <- unified.table[, c(per.object.cols, class.table.cols, per.image.cols )]
        #save the unified table to the connection directory
        write.csv(unified.table, paste(database.connection$directory, "unified_table.csv"))
        return(colnames(unified.table)) #return colnames, verification of the join
        
    }
    else
    {
        stop("The specified class of database connection is not available for use here...")
    }
    
    
}




###############################################################################
### chunk number : joinTables
# (? implement only for mysql)
# Try making a generic version of this where you can specify the tables to join,
#   and the columns to join arguements should be multiples of 
#   {<tablename, columns, keys>, <tablename, columns, keys> ...} tables are 
#   joined in order they appear in the listOfTablesInfo, i.e. 
#   a+b+c... therefore keys are required in an appropriate order!
#   Keys for a are used to join b, keys for b are used to join c, keys for the 
#   last table can be omitted. Intersecting keys will remain the ImageNumber and
#   ObjectNumber ...which may need to be specified?

# Prob should define an object to hold tablename, col and keys? # TODO?
#e.g.
#
## NOT TESTED
# list 
###############################################################################
# joinTables <- function(listOfTableInfo=list(c("tablename"="", "columns"=c(""), "keys"=c(""))), output.table.name)
# {
    # 
    # # if any of the tables are "" select all the table columns from the database
    # for(i in listOfTablesInfo)
    # {
        # all.columns <- dbGetQuery(database.connection, statement=paste("DESCRIBE ", i))$Field
        # 
        # 
        # ## TODO 
        # 
    # }
    # 
    # 
    # #select all non-redundant columns
    # cols <- NULL
    # for(i in listOfTableInfo)
    # {
        # cols <- append(cols, i$columns)  
    # }
    # #require non-redundant list of names
    # cols <- unique(cols)
    # 
    # #paste join statements
    # sql.cmd <- paste("CREATE TABLE ", output.table.name, " SELECT ", sep="")
    # paste(sql.cmd, paste(cols, sep=", ", collapse=""), sep=" ")
    # 
    # #build sql.command
    # for(i in seq(along=listOfTablesInfo))
    # {
        # if( i < (length(listOfTablesInfo)-1) ) ## while not last
        # {
            # sql.cmd <- paste(sql.cmd, " FROM (", listOfTablesInfo[i, "tablename"], " LEFT JOIN ", listOfTablesInfo[i+1, "tablename"], " USING (",  listOfTablesInfo[i, "keys"], ")")
        # }
        # else
        # {
               # #closeing sql
               # sql.cmd <- paste(sql.cmd, " LEFT JOIN ", listOfTablesInfo[i, "tablename"], " USING (", listOfTablesInfo[i-1, "keys"], ")")
        # }
    # }
    # 
    # dbGetQuery(database.connection, statement=sql.cmd)
# }



